#!/bin/bash

# 定义变量方便修改
table_db=cy_database
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

# 分析预测:岗位活跃度榜单

sql="
use cy_database;

with
tmp_business_alive1 as --没有数据支持验证 （这样做了多个当月与上月的数据，但是后续的环比中只取数据最近的日期）
(
select
	industry_code,
	posts_name,
	high_tech_talent_num, --高科技人才数
	require_talent_num, --人才需求总量
	entry_sum, --已就业人数
	quit_num, --已离职人数
	lag(require_talent_num,1) over(partition by industry_code,posts_name order by operate_time) as require_talent_num1,--上月人才需求总量
	lag(entry_sum,1) over(partition by industry_code,posts_name order by operate_time) as entry_sum1,--上月已就业人数
	lag(quit_num,1) over(partition by industry_code,posts_name order by operate_time) as quit_num1,--上月已离职人数
	operate_time
from dws_posts_alive
),


tmp_business_alive2 as
(
select
	industry_code,
	posts_name,
	high_tech_talent_num,
	require_talent_num,
	entry_sum,
	quit_num,
	require_talent_num1,
	entry_sum1,
	quit_num1,
	concat(cast(((require_talent_num-require_talent_num1)/require_talent_num1*100) as decimal(5,2)) ,'%') as require_percent,
	concat(cast(((entry_sum-entry_sum1)/entry_sum1*100) as decimal(5,2)) ,'%') as entry_percent,
	concat(cast(((quit_num-quit_num1)/quit_num1*100) as decimal(5,2)) ,'%') as wastage_percent,
	operate_time

from tmp_business_alive1
)


insert into table cy_database.ads_big_screen_result_posts_alive
select
	industry_code,
	posts_name,
	entry_sum,
	high_tech_talent_num,
	require_talent_num,
	require_percent,
	entry_percent,
	wastage_percent,
	operate_time
from tmp_business_alive2

;

"
$hive -e "$sql"